package com.isoft.demo.dao;

import com.isoft.demo.entity.Address;
import com.isoft.demo.entity.Orders;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Select;

import java.util.List;

public interface OrderDao {
    @Insert("insert into orders (ajid,asid,kind,unitprice,pweight,psum,otime,tip) values (#{ajid},#{asid},#{kind},#{unitprice},#{pweight},#{psum},#{otime},#{tip})")
    int add(Orders orders);

    @Select("SELECT oid," +
            "ajid,a1.aphone as aphone,a1.acontact as acontact,a1.address as address," +
            "asid,a2.aphone as aphone2,a2.acontact as acontact2,a2.address as address2," +
            "kind,unitprice,pweight,psum,otime,tip " +
            "FROM orders o " +
            "Inner JOIN address a1 ON a1.aid=o.ajid " +
            "Inner JOIN address a2 ON a2.aid=o.asid")
    List<Orders> search();

    @Select("SELECT oid,ajid,a1.aphone,a1.acontact,a1.address,asid,a2.aphone aphone2,a2.acontact acontact2,a2.address address2,kind,unitprice,pweight,psum,otime,tip" +
            " FROM orders o Inner JOIN address a1 ON a1.aid=o.ajid Inner JOIN address a2 ON a2.aid=o.asid" +
            " WHERE oid=#{oid}")
    List<Orders> searchById(int oid);

    @Select("select * from address")
    List<Address>searchAddress();

    @Insert("insert into address(address,cid,acontact,aphone) values (#{address},#{cid},#{acontact},#{aphone})")
    int addAddress(Address address);

    @Select("SELECT a1.acontact as acontact,a1.aphone as aphone,a1.address as address,a2.acontact as acontact2,a2.aphone as aphone2,a2.address as address2,kind,unitprice,pweight,psum,otime,tip FROM orders o Inner JOIN address a1 ON a1.aid=o.ajid Inner JOIN address a2 ON a2.aid=o.asid where a1.cid=#{cid}")
    List<Orders> queryByCid(int cid);

    @Select("SELECT SUM(psum) as psum FROM orders o Inner JOIN address a1 ON a1.aid=o.ajid where a1.cid=#{cid}")
    List<Orders>sumByCid(int cid);
}
